/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/*
 * IbopePanel.java
 *
 * Created on 05/12/2011, 04:18:06
 */
package proj5labbd.graphics.consultas;

import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;
import javax.swing.table.DefaultTableModel;
import proj5labbd.DBInterface;
import proj5labbd.graphics.abstractOpGraphics.OperationPanel;
import proj5labbd.graphics.comercial.ComercialFrame;

/*
 * Painel destinado a consulta pré-definidas pelo desenvolvedor
 * A consulta se baseia em apresentar o ibope médio dos programas cadastrados
 */
public class ProgPanel extends OperationPanel {

    /** Creates new form IbopePanel */
    public ProgPanel(ProgFrame frame, DBInterface dbInterface) {
        super(frame,dbInterface);
        try {
            initComponents();
            jbtOk.addActionListener(new IbopeAction());
            Statement stmt = dbInterface.getConn().createStatement();
                stmt.executeQuery(query1);
                stmt.executeQuery(query2);
                stmt.executeQuery(query3);
                stmt.executeQuery(query4);
        } catch (SQLException ex) {
            frame.printException(ex);
            JOptionPane.showMessageDialog(null, "Não foi possível criar os views.");
        }
    }

    /** This method is called from within the constructor to
     * initialize the form.
     * WARNING: Do NOT modify this code. The content of this method is
     * always regenerated by the Form Editor.
     */
    @SuppressWarnings("unchecked")
                                  
      
    // <editor-fold defaultstate="collapsed" desc="Generated Code">//GEN-BEGIN:initComponents
    private void initComponents() {

        jLabel1 = new javax.swing.JLabel();
        jScrollPane1 = new javax.swing.JScrollPane();
        jtbProg = new javax.swing.JTable();
        jbtOk = new javax.swing.JButton();

        addComponentListener(new java.awt.event.ComponentAdapter() {
            public void componentShown(java.awt.event.ComponentEvent evt) {
                formComponentShown(evt);
            }
        });

        jLabel1.setText("Programas e seus ibopes");

        jtbProg.setModel(new javax.swing.table.DefaultTableModel(
            new Object [][] {

            },
            new String [] {

            }
        ));
        jScrollPane1.setViewportView(jtbProg);

        jbtOk.setText("Ok");

        javax.swing.GroupLayout layout = new javax.swing.GroupLayout(this);
        this.setLayout(layout);
        layout.setHorizontalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addContainerGap()
                .addComponent(jLabel1)
                .addContainerGap())
            .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup()
                .addContainerGap()
                .addComponent(jbtOk, javax.swing.GroupLayout.PREFERRED_SIZE, 90, javax.swing.GroupLayout.PREFERRED_SIZE))
            .addComponent(jScrollPane1, javax.swing.GroupLayout.DEFAULT_SIZE, 400, Short.MAX_VALUE)
        );
        layout.setVerticalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addContainerGap()
                .addComponent(jLabel1)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(jScrollPane1, javax.swing.GroupLayout.DEFAULT_SIZE, 244, Short.MAX_VALUE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(jbtOk))
        );
    }// </editor-fold>//GEN-END:initComponents

    //atuação da tabela após a apresentação do painel ao usuário
    private void formComponentShown(java.awt.event.ComponentEvent evt) {//GEN-FIRST:event_formComponentShown
        // TODO add your handling code here:
        try {
            // TODO add your handling code here:
            setupTable();
        } catch (SQLException ex) {
            Logger.getLogger(ProgPanel.class.getName()).log(Level.SEVERE, null, ex);
        }
    }//GEN-LAST:event_formComponentShown

    // Variables declaration - do not modify//GEN-BEGIN:variables
    private javax.swing.JLabel jLabel1;
    private javax.swing.JScrollPane jScrollPane1;
    private javax.swing.JButton jbtOk;
    private javax.swing.JTable jtbProg;
    // End of variables declaration//GEN-END:variables

    //configura a(s) tabela(s) resgantando informações da base de dados
    private void setupTable() throws SQLException {
            
            Statement stmt = dbInterface.getConn().createStatement();
            
            ResultSet rs = stmt.executeQuery(select);
            
            String [][] sArray = dbInterface.rsToStringArray(rs);
            
            DefaultTableModel model = new DefaultTableModel(sArray,
                    new String[] {"Nome do programa","Horário de início","Ibope do episódio"});
           jtbProg.setModel(model); 
    }
    
    //Classe ouvinte da ação do botão OK
    class IbopeAction implements ActionListener {

        //ação do botão OK
        public void actionPerformed(ActionEvent e) {

            if (e.getSource() == jbtOk) {
                frame.dispose();
                
            }

        }


    }
    
    //Script SQL - Pré definição das consultas
    private final String query1 = "CREATE OR REPLACE VIEW nov AS "+
                                     "SELECT  titulopr AS NOME,  horainicioexno AS HORARIO_DE_INICIO , ibopeexno AS IBOPE_DO_EPISODIO FROM novela "+
                                     "JOIN exibicaonovela ON novela.idpr = exibicaonovela.idpr";
    private final String query2 = "CREATE OR REPLACE VIEW ser AS "+
                                     "SELECT  titulopr, horainicioexse , ibopeexse FROM serie "+
                                     "JOIN exibicaoserie ON serie.idpr = exibicaoserie.idpr ";
    private final String query3 = "CREATE OR REPLACE VIEW jor AS "+
                                     "SELECT  titulopr, horainicioexjo, ibopeexjo FROM jornal "+
                                     "JOIN exibicaojornal ON jornal.idpr = exibicaojornal.idpr";
    private final String query4 =  "CREATE OR REPLACE VIEW fil AS "+
                                     "SELECT  titulopr, horainicioexfi, ibopeexfi FROM filme "+
                                     "JOIN exibicaofilme ON filme.idpr = exibicaofilme.idpr ";
    private final String select = "SELECT * FROM nov "+
                                  "UNION "+
                                  "SELECT * FROM ser "+
                                  "UNION "+
                                  "SELECT * FROM jor "+
                                  "UNION "+
                                  "SELECT * FROM fil";
    }
